Today we will be going over a simple but interesting concept. Many a time, I have come across the lack of understanding on how the transactions work in SQL Server. Today we will go over a small but interesting observation. One of my clients had recently invited me to help them out with an interview for their senior developers. I had interviewed nearly 50+ candidates in a single day. There were many different questions, but the following question was incorrectly answered most of the time.
The question was to create a scenario where you can see the SCHEMA LOCK. The interview panel initially thought that this might be a very easy question for this particular interview. I, however, insisted them to keep this question for time being and then remove it from the list of interview questions only when at least 10 candidates got it right. Contrary to our expectations, we never reached a point where we had to remove this question from the list!
Let us see a simple example regarding how to create a schema lock. The answer I was looking for is as follows: create a situation where the Schema is modified in the transaction and check the status of the object or session before the transactions are committed or rolled back.
Run the following code in Query Session 1:
USE AdventureWorks
GO
BEGIN TRANSACTION
GO
CREATE PROCEDURE mySP
AS
SELECT 1
GO
SELECT OBJECT_ID('mySP') ObjectID
GO
The above script will give us the objectID of the created stored procedure. In this case, the received ObjectID is 1300199682; this can be different for your execution.
Run the following code in Query Session 2:
USE AdventureWorks
GO
SELECT *
FROM sys.procedures
GO
This query will never finish running as in Session 1, where we have created the Stored Procedure. The name is already listed in the sys.procedures, but the transactions in Session1 are not yet committed.
If you run the following code, it will also not return any results even though we have received the ObjectID in Session 1.
USE AdventureWorks
GO
SELECT OBJECT_NAME(1300199682)
GO
Run the following code in Query Session 3:
Now to confirm that a schema lock is created, we can check the dynamic management views dm_tran_locks.
USE AdventureWorks
GO
SELECT *
FROM sys.dm_tran_locks
GO
We can clearly see from the example that there is a Sch-M (schema modify) lock over our ObjectID.
You can specify the where condition to this DMV as we are know the ObjectID here.
USE AdventureWorks
GO
SELECT request_type, request_mode, resource_associated_entity_id, request_type
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = 1300199682
GO
From above example, it is very clear that running DML code in the transactions create a schema modification lock until the transactions are over.
If you run the COMMIT or ROLLBACK statement in Session 1, the Queries in Session 2 will complete right away.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
It’s good to understand transaction.
Thnks lot.
When i placed “Truncate Table ” in place of “CREATE PROCEDURE mySP” in first session, it didn’t lock my schema ,whom the table belongs to.
If Truncate Table is a DDL statement, why its behaving like this. But when i executed Drop Table or Create Table , schema locking occurring normally.
Good article. This question could stump folks who have not paid attention to Schema locks, usually the tendency is to look for the regular locks and exclusive locks. Also this brings in the concept of understanding transactions.
What a great question Pinal.
It appears simple at first but actually requires some thought and problem solving. Important traits in a good SQL Developer or DBA.
On the subject of interviewing SQL Server data professionals, you may be interested to read my “Top 10 Junior DBA Interview Tips”.
This question was asked in my interview and i replied exactly as written here.My interviewer was impressed and i got into infosys.Thanks.
We want to know the details explation that how locks work when DDL operation is going on in a database
Pinal,
Can you help explain the following:
SESSION 52:
use tempdb
go
create table tableA (col1 int identity(1,1) not null primary key)
go
begin tran
insert into tableA default values
waitfor delay ‘1:00’
rollback tran
SESSION 53:
USE TEMP
GO
alter table tableA add col2 int
SESSION 55:
insert into tableA default values
/* insert is immediately successful */
SESSION 58:
EXEC SP_LOCK (omiited some data to make it viewable)
spid ObjId Type Mode Status
52 938157062 TAB IX GRANT
52 938157062 PAG IX GRANT
52 938157062 KEY X GRANT
53 938157062 TAB Sch-M WAIT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
58 1131151075 TAB IS GRANT
So, the inserts require a table level IX lock and aren’t blocked by the table level Sch-M lock.
Let’s try this again with a DELETE.
SESSION 52:
USE tempdb
GO
drop table tableA
GO
create table tableA (col1 int identity(1,1) not null primary key)
go
insert into tableA default values
insert into tableA default values
insert into tableA default values
GO
SESSION 53:
BEGIN TRAN
DELETE TABLEA WHERE COL1=1
WAITFOR DELAY ‘1:00’
ROLLBACK TRAN
SESSION 55:
ALTER TABLE TABLEA ADD COL2 INT
SESSION 57:
DELETE TABLEA WHERE COL1=1
/* NOW IT’S BLOCKED */
SESSION 61:
spid dbid ObjId Type Mode Status
53 2 0 PAG IX GRANT
53 2 1002157290 TAB IX GRANT
53 2 0 KEY X GRANT
54 7 0 DB S GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M WAIT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
55 2 1002157290 TAB Sch-M GRANT
57 2 1002157290 TAB IX WAIT
61 7 0 DB S GRANT
61 1 1131151075 TAB IS GRANT
62 7 0 DB S GRANT
So, the insert was fine as we saw before but now with a DELETE.. it’s blocked… Note the wait for the TAB level, IX on session 57. Session 57 is blocked by 55, the alter table statement. But why didn’t this occur on the INSERT, in the previous example.
In addition, the lock compatibility matrix says that IX and SCH-M locks are not compatible. As a result, the ALTER should not have granted any SCH-M locks on the same table object.
I tried this with UPDATES and the result is the same as with DELETES.
Corrected some typos…
Pinal,
Can you help explain the following:
SESSION 52:
use tempdb
go
create table tableA (col1 int identity(1,1) not null primary key)
go
begin tran
insert into tableA default values
waitfor delay ‘1:00’
rollback tran
SESSION 53:
USE TEMPDB
GO
alter table tableA add col2 int
SESSION 55:
insert into tableA default values
/* insert is immediately successful */
SESSION 58:
EXEC SP_LOCK (omiited some data to make it viewable)
spid ObjId Type Mode Status
52 938157062 TAB IX GRANT
52 938157062 PAG IX GRANT
52 938157062 KEY X GRANT
53 938157062 TAB Sch-M WAIT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
53 938157062 TAB Sch-M GRANT
58 1131151075 TAB IS GRANT
So, the inserts require a table level IX lock and aren’t blocked by the table level Sch-M lock.
Let’s try this again with a DELETE.
SESSION 52:
USE tempdb
GO
drop table tableA
GO
create table tableA (col1 int identity(1,1) not null primary key)
go
insert into tableA default values
insert into tableA default values
insert into tableA default values
GO
SESSION 53:
BEGIN TRAN
DELETE TABLEA WHERE COL1=1
WAITFOR DELAY ‘1:00’
ROLLBACK TRAN
SESSION 55:
ALTER TABLE TABLEA ADD COL2 INT
SESSION 57:
DELETE TABLEA WHERE COL1=2
/* NOW IT’S BLOCKED */
SESSION 61:
spid dbid ObjId Type Mode Status
53 2 0 PAG IX GRANT
53 2 1066157518 TAB IX GRANT
53 2 0 KEY X GRANT
53 2 1002157290 TAB IX GRANT
54 7 0 DB S GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M WAIT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
55 2 1066157518 TAB Sch-M GRANT
57 2 1066157518 TAB IX WAIT
61 7 0 DB S GRANT
61 1 1131151075 TAB IS GRANT
62 7 0 DB S GRANT
So, the insert was fine as we saw before but now with a DELETE.. it’s blocked… Note the wait for the TAB level, IX on session 57. Session 57 is blocked by 55, the alter table statement. But why didn’t this occur on the INSERT, in the previous example.
In addition, the lock compatibility matrix says that IX and SCH-M locks are not compatible. As a result, the ALTER should not have granted any SCH-M locks on the same table object.
I tried this with UPDATES and the result is the same as with DELETES.